from openpyxl import load_workbook, Workbook
from rich import print as rprint
from pathlib import Path
import json
from win32com.client import Dispatch

from PySide6.QtCore import Signal, QObject

def get_value(value):
    if value is None:
        raise ValueError("输入值不能为空。")
    if isinstance(value, float):
        return value
    elif isinstance(value, str):
        try:
            return float(value.replace(',', ''))
        except ValueError:
            raise ValueError(f"无法将输入值 '{value}' 转换为浮点数。")

class GetFiles(QObject):
    value_signal = Signal(dict)
    rel_tip_signal = Signal(str, str)
    finished_signal = Signal(int)

    def __init__(self, path_list: list, file_type: str, file_ext: list[str]=['xlsx']):
        super().__init__()
        self.path_list = path_list
        self.file_type = file_type
        self.file_ext = file_ext

    def run(self):
        first_path = Path(self.path_list[0])
        if first_path.is_dir():
            all_files = []      # 获取指定扩展名的所有文件
            for ext in self.file_ext:
                all_files += list(first_path.glob(f'*.{ext}'))
            if len(all_files) == 0:
                self.rel_tip_signal.emit(f'{first_path} 中没有找到指定格式文件（{"、".join(self.file_ext)}），检查路径是否正确', 'red')
                self.finished_signal.emit(0)
                return     # 如果没有找到指定格式的文件，则提示并返回
        else:
            all_files = [Path(path) for path in self.path_list]
        files = {}      # 根据文件类型从文件名中筛选
        for xlsx_file in all_files:
            xlsx_file_name = xlsx_file.name
            if f'项目-{self.file_type}-20' not in xlsx_file_name:
                self.rel_tip_signal.emit(f'{xlsx_file_name} 不是 {self.file_type}，已跳过！', 'red')
                continue
            progromme_name = xlsx_file.stem.split('-')[0]
            files[progromme_name] = xlsx_file
        if len(files) == 0:
            self.rel_tip_signal.emit(f'{self.root_path} 中没有找到 {self.file_type} 文件，检查路径是否正确', 'red')
            self.finished_signal.emit(0)
            return
        self.value_signal.emit(files)
        self.finished_signal.emit(1)
        return files

class ReadLedger(QObject):
    value_signal = Signal(dict)
    rel_tip_signal = Signal(str, str)
    finished_signal = Signal(int)
    log_signal = Signal(str)

    def __init__(self, ledger_path: str, sheet_name: str='汇总表', content_start: int=3):
        super().__init__()
        self.ledger_path = Path(ledger_path)
        self.sheet_name = sheet_name
        self.content_start = content_start

        self.root_path = self.ledger_path.parent
        self.programme_name = self.ledger_path.stem.split('-')[0]

    def run(self):
        try:
            wb = load_workbook(self.ledger_path, data_only=True)
        except PermissionError:
            self.rel_tip_signal.emit(f'{self.ledger_path} 文件没有读写权限，请检查！', 'red')
            self.finished_signal.emit(0)
            return
        try:
            ws = wb[self.sheet_name]
        except KeyError:
            self.rel_tip_signal.emit('{self.ledger_path} 中没有找到名为 {self.sheet_name} 的工作表，请检查！', 'red')
            self.finished_signal.emit(0)
            return
        self.rel_tip_signal.emit(f'开始读取 {self.ledger_path.name} {self.sheet_name}', 'green')
        headers_info = [(cell.column, cell.value) for cell in ws[self.content_start-1]]
        headers = [header[1] for header in headers_info]
        content = {}
        blank_row = []
        bug_row = []
        duplicates = []
        for row in ws.iter_rows(min_row=self.content_start):
            row_data = [str(cell.value) for cell in row]        # str()：如果有格式为日期的单元格，避免程序识别为日期类型的数据
            no_repeat_row_data = set(row_data)
            real_row_data = list(filter(lambda x: x != 'None', row_data))
            if len(no_repeat_row_data) < 5 or len(real_row_data) < 9:
                blank_row.append(row[0].row)
                continue
            if self.sheet_name == '汇总表':
                unit_content_key = row_data[3]
            else:       # 明细表
                unit_content_key = row_data[1] + row_data[2]
            if unit_content_key in ['None', 'NoneNone']:
                unit_content_key = row_data[0]
                if unit_content_key == 'None':
                    if all(x == None for x in row_data[:6]) == True:
                        blank_row.append(row[0].row)        # 如果这一行前六个单位格都为空，则判定为空行
                    else:
                        bug_row.append(row[0].row)
                    continue
            if unit_content_key in ['往来单位名称', '分包小计', '劳务小计', '材料小计', '其他小计', '汇总', '租赁小计', '合计', '材料商小计', '总计', '序号', '合约小计', '物资小计', '租赁费小计']:
                duplicates.append((self.programme_name, self.sheet_name, unit_content_key, '所在行：', row[0].row))
                continue
            if unit_content_key in list(content):       # 避免相同的键覆盖原来的值
                unit_content_key = f'{unit_content_key}_{row[0].row}'
            content[unit_content_key] = dict(zip(headers, row_data))
        # json_data = json.dumps(content, indent=2, ensure_ascii=False)
        # with open(self.ledger_path / f'{self.progromme_name}_{self.sheet_name}_ledger_content.json', 'w', encoding='utf-8') as f:
        #     f.write(json_data)
        if blank_row:
            self.log_signal.emit(f'共跳过 {len(blank_row)} 行数据，行号分别为 {blank_row}')
        if bug_row:
            self.log_signal.emit(f'共跳过 {len(bug_row)} 行数据有误，行号分别为 {bug_row}')
        if duplicates:
            self.log_signal.emit(f'共发现 {len(duplicates)} 个重复数据，重复数据信息为：{duplicates}')
        self.value_signal.emit(content)
        self.finished_signal.emit(1)
        return content

def read_unit_by_balance(file_path, column_letter='H'):
    """
    从科目余额表中读取往来单位信息
    """
    wb = load_workbook(file_path, data_only=True)
    ws = wb.active
    unit_list = []
    numbers = [c.value for c in ws['A'][5:]]
    names = [c.value for c in ws['B'][5:]]
    acount = [get_value(c.value) for c in ws[column_letter][5:]]

    unit_list = {}    # 往来单位的余额数据
    for i, num in enumerate(numbers):
        if num.startswith('BP'):
            unit_list[num] = {'name': names[i], 'account': acount[i]}
    return unit_list

def just_open(file_path):
    #模拟手工打开excel表，不支持wps表格
    try:
        xlapp=Dispatch("Excel.Application")
        xlapp.Visible=False
        xlbook=xlapp.Workbooks.Open(file_path)
        xlbook.Save()
        xlbook.Close()
    except:
        print('请手工打开 %s 后，随便修改一个空值并保存'%file_path)

def collect_sheet(current_path, sheet_name='数据汇总'):
    xlsx_files = current_path.glob('*项目-结算支付台账-20*.xlsx')       # 获取当前路径下所有的xlsx文件
    info = {}
    for xlsx_file in xlsx_files:
        xlsx_name = xlsx_file.stem
        programme_name = xlsx_name.split('-')[0]
        wb = load_workbook(xlsx_file, data_only=True)
        ws = wb[sheet_name]
        info[programme_name] = [[cell.value for cell in row] for row in ws['A8':'F11']]         # 获取A8:F11 的数据
        print(f'{programme_name} 读取完毕')

    # 将读取到的数据写入到新文件中
    new_xlsx_file = current_path / '汇总.xlsx'
    wb = Workbook()
    ws = wb.active
    for programme_name, data in info.items():
        for row in data:
            row.insert(0, programme_name)
            ws.append(row)

    wb.save(new_xlsx_file)
    print(f'数据已写入到 {new_xlsx_file} 中')